package main.staff;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStream;

import main.util.SqlConstants;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;


public class CreateSql {

	public static FileWriter staffInfo,role,serviceType,password,orm;
	static FileWriter del;
	
	public static void main(String[] args) {
		InputStream inp;
		try {
			inp = new FileInputStream("D:/workbook30.xlsx");
			//InputStream inp = new FileInputStream("workbook.xlsx");
			
			Workbook wb = WorkbookFactory.create(inp);
			Sheet sheet = wb.getSheetAt(0);

			 staffInfo = new FileWriter("D:/人员信息t_ucp_staffbasicinfo.sql");
			 role= new FileWriter("D:/角色t_ucp_staffrole.sql");
			 serviceType=new FileWriter("D:/业务类型t_ucp_staffserviceType.sql");
			 password=new FileWriter("D:/密码t_ucp_password.sql");
			 orm=new FileWriter("D:/映射t_ucp_staff_orm_kf.sql");
			 
			 del=new FileWriter("D:/删除.sql");
			
			 CreateTypesSql.topWrite(staffInfo);
			 CreateTypesSql.topWrite(role);
			 CreateTypesSql.topWrite(serviceType);
			 CreateTypesSql.topWrite(password);
			 CreateTypesSql.topWrite(del);
			 CreateTypesSql.topWrite(orm);
			 
			 operateSheet(sheet);
			 
			 SqlConstants.topWriteEnd(staffInfo);
			 SqlConstants.topWriteEnd(role);
			 SqlConstants.topWriteEnd(serviceType);
			 SqlConstants.topWriteEnd(password);
			 SqlConstants.topWriteEnd(orm);
			 SqlConstants.topWriteEnd(del);
			 
			staffInfo.close();
			role.close();
			serviceType.close();
			password.close();
			orm.close();
			del.close();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			
		}
	}
	
	public static void operateSheet(Sheet sheet){
		try {
			for(int i=0;i<sheet.getLastRowNum()+1;i++){
				Row row = sheet.getRow(i);
				String staffid=CreateTypesSql.removeNull(row, 4);
				
				if(staffid.matches("[a-zA-Z]{2}[0-9]{5}") || staffid.matches("[a-zA-Z]{0}?[0-9]{4}?")){
					staffInfo.write(CreateTypesSql.staffbasicinfo(row,staffid));
					role.write(CreateTypesSql.staffrole(row,staffid));
					serviceType.write(CreateTypesSql.staffserviceType(row,staffid));
					password.write(CreateTypesSql.password(staffid));
					orm.write(CreateTypesSql.ormSql(row, staffid));
					
					del.write("delete from t_ucp_staffservicetype where staffid='"+staffid+"';\n");
					del.write("delete from t_ucp_staffbasicinfo where staffid='"+staffid+"';\n");
					del.write("delete from t_ucp_staffrole where staffid='"+staffid+"';\n");
					del.write("delete from t_ucp_password where staffid='"+staffid+"';\n");
					
					System.out.println(staffid+":成功   "+i);
				}else{
					System.out.println(staffid+":loss  "+i);
				}
			}
		} catch (IOException e) {
			System.out.println("'''''''''''''''''''''''''"+sheet.getSheetName()+"添加失败");
			e.printStackTrace();
		}
	}
	
	public static void main2(String[] args){
		System.out.println(CreateTypesSql.password("0682"));
	}
	
}
